package jdbc.dao.soiree;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

import jdbc.DAO;
import metier.soiree.Soiree;
import metier.soiree.TypeSoiree;

public class SoireeDAO extends DAO<Soiree> {

	@Override
	public Soiree find(int id) {
		Soiree soiree=null ;
		try {
			ResultSet result = DAO.connect
					.createStatement(
							ResultSet.TYPE_SCROLL_INSENSITIVE,
							ResultSet.CONCUR_UPDATABLE
							).executeQuery(
									"SELECT * FROM SOIREE WHERE IdSoiree = " + id
									);
			if(result.first()){
				soiree = new Soiree(
						id,
						result.getString("Intitule").trim(),
						result.getDate("Date_Soiree"),
						TypeSoiree.getEnum(result.getString("Statut").trim())
						);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return soiree;
	}

	public ArrayList<Soiree> findIntitule(String intitule) {
		ArrayList<Soiree> soiree = new ArrayList<Soiree>();
		try {
			ResultSet result = DAO.connect
					.createStatement(
							ResultSet.TYPE_SCROLL_INSENSITIVE,
							ResultSet.CONCUR_UPDATABLE
							).executeQuery(
									"SELECT * FROM SOIREE WHERE INSTR(LOWER(Intitule),'"+ intitule +"')>0"
									);

			while(result.next()){
				soiree.add(new Soiree(
						result.getInt("IdSoiree"),
						result.getString("Intitule").trim(),
						result.getDate("Date_Soiree"),
						TypeSoiree.getEnum(result.getString("Statut").trim())
						));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return soiree;
	}
	
	public ArrayList<Soiree> findDate(Date date) {
		ArrayList<Soiree> soiree = new ArrayList<Soiree>();
		try {
			ResultSet result = DAO.connect
					.createStatement(
							ResultSet.TYPE_SCROLL_INSENSITIVE,
							ResultSet.CONCUR_UPDATABLE
							).executeQuery(
									"SELECT * FROM SOIREE WHERE DATE_SOIREE = TO_DATE(' "+new java.sql.Date(date.getTime())+" ','YYYY-MM-DD')"
									);

			while(result.next()){
				soiree.add(new Soiree(
						result.getInt("IdSoiree"),
						result.getString("Intitule").trim(),
						result.getDate("Date_Soiree"),
						TypeSoiree.getEnum(result.getString("Statut").trim())
						));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return soiree;
	}

	@Override
	public Soiree create(Soiree obj) {
		try {
			ResultSet result = DAO.connect
					.createStatement(
							ResultSet.TYPE_SCROLL_INSENSITIVE,
							ResultSet.CONCUR_UPDATABLE
							).executeQuery(
									"SELECT soiree_id_seq.nextval as IdSoiree from dual"
									);
			if(result.first()){
				int id = result.getInt("IdSoiree");
				java.sql.Date dateSql = new java.sql.Date(obj.getDate().getTime());
				PreparedStatement prepare = DAO.connect
						.prepareStatement("INSERT INTO Soiree (IdSoiree, Intitule, Statut, Date_Soiree) VALUES(?,?,?,TO_DATE(' "+dateSql+" ','YYYY-MM-DD'))"
								);
				prepare.setInt(1, id);
				prepare.setString(2, obj.getIntitule());
				prepare.setString(3, obj.getStatut().getString());
				prepare.executeUpdate();
				obj = this.find(id);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return obj;
	}

	@Override
	public Soiree update(Soiree obj) {
		try {
			DAO.connect
			.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE
					).executeUpdate(
							"UPDATE Soiree SET Intitule = '" + obj.getIntitule() + "'"+									
									", Date_Soiree = TO_DATE(' "+ new java.sql.Date(obj.getDate().getTime()) +" ','YYYY-MM-DD') + "+								
									", Statut = '"+ obj.getStatut().getString() + "'"+ 
									" WHERE IdSoiree = " + obj.getID_SOIREE()
							);
			obj = this.find(obj.getID_SOIREE());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return obj;
	}

	@Override
	public void delete(Soiree obj) {
		try {
			DAO.connect
			.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE
					).executeUpdate(
							"DELETE FROM Soiree WHERE IdSoiree = " + obj.getID_SOIREE()
							);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}
